package com.zh.harmony.ormlitesample.dao;

import com.zh.harmony.ormlitesample.entity.UserEntity;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @author wally
 * @date 2022/08/11
 * <p>
 * User表Dao层
 */
public class UserDao extends BaseDao<UserEntity, Long> {
    public UserDao() {
        this(UserEntity.class);
    }

    private UserDao(Class<UserEntity> entityClazz) {
        super(entityClazz);
    }

    /**
     * 根据昵称查询
     */
    public List<UserEntity> queryByNickName(String nickname) {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    //倒序
                    .orderBy("id", false)
                    .where()
                    .eq("nickname", nickname)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询所有，并升序排序
     */
    public List<UserEntity> queryAllAsc() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    //参数false表示降序，true表示升序。
                    .orderBy("id", true)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询所有，并降序排序
     */
    public List<UserEntity> queryAllDesc() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    //参数false表示降序，true表示升序。
                    .orderBy("id", false)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询年龄小于18岁的用户
     */
    public List<UserEntity> queryLessThan18() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .orderBy("id", false)
                    .where()
                    .lt("age", 18)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询年龄大于30的用户
     */
    public List<UserEntity> queryGreaterThan30() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .gt("age", 30)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询年龄大于等于35的用户
     */
    public List<UserEntity> queryGreaterThanOrEqualsTo35() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .ge("age", 35)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询年龄小于等于20的用户
     */
    public List<UserEntity> queryLessThanOrEqualsTo20() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .le("age", 20)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询年龄不等于18岁的用户
     */
    public List<UserEntity> queryNotEqualsTo18() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .ne("age", 18)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询某些年龄的用户
     */
    public List<UserEntity> queryByInAges(List<Integer> ages) {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .in("age", ages.toArray())
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询非某些年龄的用户
     */
    public List<UserEntity> queryByNotInAges(List<Integer> ages) {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .notIn("age", ages.toArray())
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 根据昵称前缀，模糊查询
     */
    public List<UserEntity> queryByLikeNicknamePrefix(String prefix) {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .like("nickname", prefix + "%")
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 根据昵称，模糊查询
     */
    public List<UserEntity> queryByLikeNickname(String nickname) {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .like("nickname", "%" + nickname + "%")
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 根据昵称后缀，模糊查询
     */
    public List<UserEntity> queryByLikeNicknameSuffix(String suffix) {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .like("nickname", "%" + suffix)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询18到30之间的用户
     */
    public List<UserEntity> queryByBetween() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .between("age", 18, 30)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * where and 子句-与操作，查询id为1或id为2的用户
     */
    public List<UserEntity> queryByAnd() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .eq("id", 1)
                    .and()
                    .eq("id", 2)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * where or 子句-或操作，查询25岁或18岁的用户
     */
    public List<UserEntity> queryByOr() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .where()
                    .eq("age", 25)
                    .or()
                    .eq("age", 18)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * distinct 按指定列去重，只返回1个
     */
    public List<UserEntity> queryByDistinct() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .selectColumns("nickname", "Wally")
                    .distinct()
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * groupBy 按指定列分组
     */
    public List<UserEntity> queryByGroupBy() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .groupBy("city")
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * offset 分页查询
     */
    public List<UserEntity> queryByOffset() {
        //offset跳过指定的行数
        //limit限制获取指定行数
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .offset(2L)
                    .limit(2L)
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * Having 分组后，进行聚合函数（SUM, COUNT, MAX, AVG）运算
     * 查询某个城市，年龄大于18岁的用户
     */
    public List<UserEntity> queryByHaving() {
        try {
            List<UserEntity> list = mDao.queryBuilder()
                    .groupBy("city")
                    .having("SUM(age) > 18")
                    .query();
            if (list == null) {
                list = new ArrayList<>();
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 查询总行数
     */
    public long queryByCount() {
        try {
            return mDao.queryBuilder().countOf();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 查询出第一个昵称叫Wally的用户
     */
    public UserEntity queryByFirst() {
        try {
            return mDao.queryBuilder()
                    .where()
                    .like("nickname", "Wally")
                    .queryForFirst();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 查询结果返回Iterator
     */
    public Iterator<UserEntity> queryByIterator() {
        try {
            return mDao.queryBuilder().query().iterator();
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<UserEntity>().iterator();
        }
    }
}